﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web.Mvc;
using System.Linq;
using System.Web.Script.Serialization;
using BLL;
using Model;
using Common;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace WebPortal.Controllers
{
    public class GenericSearchController : BaseController
    {
        #region "模型搜索"

        public ActionResult ModelIndex()
        {
            return View();
        }
        /// <summary>
        /// 取模型信息
        /// </summary>
        /// <param name="id">模型id</param>
        /// <returns></returns>
        public ActionResult ModelItem(string id, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ModelConfigBLL bll = new ModelConfigBLL(loginInfos);
            ModelConfig model = bll.GetItem(id);
            return View(model);
        }
        /// <summary>
        /// 取模型列表
        /// </summary>
        /// <param name="collection">提交数据</param>
        /// <returns></returns>
        public ActionResult GetModelConfigListByModelTypeID(FormCollection collection, string dbName)
        {
            Dictionary<string, string> model = collection["model"].FromJSon<Dictionary<string, string>>();
            IDataParameter[] queryParas = new SqlParameter[model.Count];
            int index = 0;
            foreach (string item in model.Keys)//解析数据
            {
                queryParas[index] = new SqlParameter("@" + item, model[item]);
                index += 1;
            }
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ModelConfigBLL bll = new ModelConfigBLL(loginInfos);
            return Json(bll.GetList(queryParas));//列表数据
        }
        /// <summary>
        /// 取模型字段
        /// </summary>
        /// <param name="id">模型id</param>
        /// <returns></returns>
        public ActionResult GetModelColumns(string id, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ModelConfigBLL bll = new ModelConfigBLL(loginInfos);
            return Json(bll.GetModelColumns(id));
        }

        #endregion

        #region "DQ搜索"

        public ActionResult DQIndex()
        {
            return View();
        }
        /// <summary>
        /// 取dq信息
        /// </summary>
        /// <param name="schemaName">架构名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public ActionResult DQItem(string schemaName, string tableName)
        {
            TableBaseInfo model = new TableBaseInfo() { SchemaName = schemaName, TableName = tableName, TableNameFull = schemaName + "." + tableName };
            return View(model);
        }
        /// <summary>
        /// 取dq表数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public ActionResult GetDQTables(string dbName, string tableName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            GenericSearchBLL genericSearchBLL = new GenericSearchBLL(loginInfos);
            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            string result = genericSearchBLL.GetDQTables(tableName);//表数据
            result = string.IsNullOrEmpty(result) ? "[]" : result;
            return Json(jsSerializer.DeserializeObject(result));
        }
        /// <summary>
        /// 获取表的数据
        /// </summary> 
        /// <returns></returns>
        public ActionResult GetTableData()
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            try
            {
                string dbName = Request.Params["dbName"];
                string order = Request.Params["order"];
                string sort = Request.Params["sort"];
                string modelID = Request.Params["modelID"];
                string DQID = Request.Params["DQID"];
                string query = Request.Params["query"];
                List<string> sql = new List<string>();
                List<SqlParameter> sqlPara = new List<SqlParameter>();
                sql.Add(" [Dq_id] = @Dq_id ");
                sqlPara.Add(new SqlParameter("@Dq_id", DQID));
                if (!string.IsNullOrEmpty(query) && query != "[]")
                {
                    query = query.Replace("\"", "").Replace("[[", "").Replace("]]", "");
                    IEnumerable<string> arrList = query.Split("],[");
                    foreach (string str in arrList)
                    {
                        string[] item = str.Split(',');
                        switch (item[1])
                        {
                            case "like":
                            case "not like":
                                sql.Add(string.Format(" [{0}] {1} '%'+@{0}+'%' ", item[0], item[1]));
                                break;
                            case "is null":
                            case "is NOT null":
                                sql.Add(string.Format(" [{0}] {1} ", item[0], item[1]));
                                break;
                            case "in":
                            case "not in":
                                sql.Add(string.Format(" [{0}] {1} (@{0}) ", item[0], item[1]));
                                break;
                            default:
                                sql.Add(string.Format(" [{0}] {1} @{0} ", item[0], item[1]));// " column=@column"
                                break;
                        }
                        sqlPara.Add(new SqlParameter("@" + item[0], item[2]));
                    }
                }

                if (string.IsNullOrEmpty(sort))
                    sort = "desc";
                if (string.IsNullOrEmpty(order))
                    order = "";

                LoginInfo loginInfos = base.loginInfo();
                loginInfos.DBName = dbName;
                GenericSearchBLL genericSearchBLL = new GenericSearchBLL(loginInfos);
                var html = new System.Text.StringBuilder();

                DataTable dt = genericSearchBLL.GetTableData(modelID, order, sort, sql.ToArray(), sqlPara.ToArray());//sourceSystemBLL.GetSourceTableData(viewName, connstr, connType, order, sort, sql.ToArray(), para);
                jsonResponse.Data = SysPublicBLL.DataTableToString(dt, 0, order, sort, false, false, true);
                jsonResponse.Head = SysPublicBLL.GetSourceTableHeadData(dt.Columns);

                jsonResponse.Success = true;
                jsonResponse.Message = "OK";
                return Json(jsonResponse);
            }
            catch (Exception ex)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "信息获取失败！" + ex.Message;
                jsonResponse.Data = "";
                return Json(jsonResponse);
            }
        }

        #endregion

        #region "公共方法"

        /// <summary>
        /// 获取操作符
        /// </summary>
        /// <returns></returns>
        public ActionResult GetOperatorList(string dbName)
        {
            return Json(OperatorBLL.GetList());
        }

        /// <summary>
        /// 获取表的所有列
        /// </summary>
        /// <param name="schemaName">表架构</param>
        /// <param name="tableName">表名称</param>
        /// <returns>[{name:'列名称',value:'null'}]</returns>
        public ActionResult GetTableColumns(string dbName, string schemaName, string tableName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL bll = new SysPublicBLL(loginInfos);
            List<SysNameValue> list = bll.GetTableInfoList(schemaName + "." + tableName);
            IList<TableColumnInfo> data = new List<TableColumnInfo>();
            foreach (var item in list)
            {
                if (!item.Name.ToUpper().Contains("GUID") && !item.Name.ToUpper().Contains("DB_ID"))
                {
                    data.Add(new TableColumnInfo() { ColumnName = item.Name, ColumnDescription = SubSpecial(item.Name), ColumnValue = item.Value, ColumnOperator = "=" });
                }
            }
            return Json(data);
        }

        public string ReplaceColumn(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return "";
            }
            else
            {
                return value.Replace("|!@#$%^&*|", "");
            }
        }

        public string SubSpecial(string data)
        {
            string specialStr = "|!@#$%^&*|";
            if (data.Contains(specialStr))
            {
                int specialStrIndex = data.IndexOf(specialStr);
                return data.Substring(specialStrIndex + specialStr.Length);
            }
            return data;
        }
        /// <summary>
        /// 获取视图的数据
        /// </summary>
        /// <param name="schemaName">架构名称</param>
        /// <param name="dataSourceName">视图名称</param>
        /// <param name="keyWords">搜索条件</param>
        /// <param name="pageSize">分页条数</param>
        /// <param name="pageIndex">页码</param>
        /// <returns></returns>
        public ActionResult GetViewData(string modelID, string dbName, string order, string sort)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            try
            {
                Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
                int rows = int.Parse(Request.Params["rows"]);
                int page = int.Parse(Request.Params["page"]);
                string query = Request.Params["query"];
                List<string> sql = new List<string>();
                IDataParameter[] sqlPara = null;
                if (!string.IsNullOrEmpty(query) && query != "[]")
                {
                    query = query.Replace("\"", "").Replace("[[", "").Replace("]]", "");
                    IEnumerable<string> arrList = query.Split("],[");

                    sqlPara = new SqlParameter[arrList.Count() + 2];
                    int i = 0;
                    foreach (string str in arrList)
                    {
                        string[] item = str.Split(',');
                        sql.Add(string.Format(" {0} {1} @{0}", item[0], item[1]));// " column=@column"
                        sqlPara[i] = new SqlParameter("@" + item[0], item[2]);
                        i++;
                    }
                    sqlPara[i] = new SqlParameter("@Rows", rows);
                    sqlPara[i + 1] = new SqlParameter("@PageNum", page);
                }
                else
                {

                    sqlPara = new SqlParameter[]{ 
                   new SqlParameter("@Rows", rows),
                   new SqlParameter("@PageNum", page)
                };
                }

                if (string.IsNullOrEmpty(sort))
                    sort = "desc";
                if (string.IsNullOrEmpty(order))
                    order = "BatchTime";

                LoginInfo loginInfos = base.loginInfo();
                loginInfos.DBName = dbName;
                ModelConfigBLL bll = new ModelConfigBLL(loginInfos);
                GenericSearchBLL genericSearchBLL = new GenericSearchBLL(loginInfos);
                ModelConfig modelInfo = bll.GetItem(modelID);
                string tableName = string.Format("[{0}].[{1}]", modelInfo.SchemaName, modelInfo.ModelTableCode);
                DataTable dt = genericSearchBLL.GetViewData(tableName, sort, order, sql.ToArray(), sqlPara);
                if (dt.Rows.Count > 0)
                {
                    jsonResponse.Data = SysPublicBLL.DataTableToString(dt, 0, order, sort, false, false, true);
                    jsonResponse.Head = SysPublicBLL.GetSourceTableHeadData(dt.Columns);
                    jsonResponse.Success = true;
                    jsonResponse.Message = "OK";
                    return Json(jsonResponse);
                }
                else
                {
                    jsonResponse.Success = false;
                    jsonResponse.Message = "读取结果无数据！";
                    return Json(jsonResponse);
                }
            }
            catch (Exception ex)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = ex.Message;
                return Json(jsonResponse);
            }
        }

        #endregion
        /// <summary>
        /// 删除dq信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public string DeleteDQ(string dbName, string tableName, string date)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            GenericSearchBLL genericSearchBLL = new GenericSearchBLL(loginInfos);
            string msg = genericSearchBLL.DeleteDQ(tableName, date);
            new SysOperationLog(loginInfos).Log("DQ搜索", Request.UserHostAddress, OperationAction.删除, OperationStatus.成功, "删除DQ记录" + tableName, "superadmin");
            return msg;
        }
    }

    /// <summary>
    /// 通用搜索条件实体
    /// </summary>
    public class WhereCondition
    {
        public string ColumnName { get; set; }
        public string ColumnOperator { get; set; }
        public string ColumnValue { get; set; }
    }

    /// <summary>
    /// DQ表基本信息
    /// </summary>
    public class TableBaseInfo
    {
        public string SchemaName { get; set; }
        public string TableName { get; set; }
        public string TableNameFull { get; set; }
    }

    public class TableColumnInfo
    {
        public string ColumnName { get; set; }
        public string ColumnDescription { get; set; }
        public string ColumnValue { get; set; }
        public string ColumnOperator { get; set; }
    }
}
